package de.gwtprime.server.sql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;

import com.google.appengine.api.rdbms.AppEngineDriver;
import com.google.cloud.sql.jdbc.internal.SQLUnknownConnectionIdException;

/**
 * The SQL Connection
 * @author jan
 *
 */
public class SQLConnect {

	
	/**
	 * Logger
	 */
	private static Logger logger = Logger.getLogger(SQLConnect.class);
	
	
	 // Hostname
    private static String dbHost = "localhost";
 
    // Port -- Standard: 3306
    private static String dbPort = "3306";
 
    // DB name
   private static String database = "stockanalyzer2";
 
    // DB user
   private static String dbUser = "root";
 
    // DB password
   private static String dbPassword = "";
   
   
   // Switch between cloudSQL/Tomcat
   public boolean useTomcatConnection = false;
	
    private static Connection conn;
    private static SQLConnect instance = null;
	
    Properties connectionProperties = new Properties();
	
	private SQLConnect(){
		
		connectionProperties.put("autoReconnect", true);
		connectionProperties.put("maxReconnects", 4);
		
		try {
			DriverManager.registerDriver(new AppEngineDriver());
			
			getConnection();
			
		} catch (Exception e) {
			logger.error("No connection to database possible", e);
		}
	}
	public static SQLConnect getInstance(){
		if(instance==null){
			instance = new SQLConnect();
		}
		
		return instance;
	}
	
	
	/**
	 * SQL Update/Insert
	 * Returns number of affected rows
	 */
	public int update(String sql) throws SQLException{
		int result = 0;
		Statement query;
        try {
            query = conn.createStatement();

                   
           result = query.executeUpdate(sql);

        } catch (SQLException e) {
        	
	        logger.error("Database update failed with query:" +sql, e);
	        if(e instanceof SQLUnknownConnectionIdException){
	        	reopenConnection();
	        	update(sql);
	        }
	        throw e;
        }
        
        
        return result;
		
		
		
	}
	
	public static void main(String[] args) {
		//getInstance().test();
	}
	
	/**
	 * Query the database with select-query
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public ResultSet select(String sql) throws SQLException{
		Statement query;
		ResultSet result = null;
	    try {
	        query = conn.createStatement();
	        result = query.executeQuery(sql);
	
	    } catch (SQLException e) {
	        logger.error("Database query failed with query: "+sql, e);
	        if(e instanceof SQLUnknownConnectionIdException){
	        	reopenConnection();
	        	select(sql);
	        }
	        throw e;
	    }
    
	    return result;
	}
	
	/**
	 * Close and reopen the connection
	 */
	public void reopenConnection(){
		try {
			conn.close();
			getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	public void getConnection() throws SQLException{
		
		if(useTomcatConnection){
			String connectionString = String.format("jdbc:mysql://%s:%s/%s?user=%s&password=%s", dbHost, dbPort, database, dbUser, dbPassword);
			conn = DriverManager.getConnection(connectionString);
		}else{
		
			conn = DriverManager.getConnection("jdbc:google:rdbms://stockanalyzer-jan:gwtdb/stockanalyzer2", connectionProperties);
		}	
	}
	
	/**
	 * Ping the database to keep instance alive -> prevent cloud sql warmup
	 * @throws SQLException
	 */
	public void ping() throws SQLException{
		conn.isValid(10000);
	}
	
}


